UBB.Developers Forums Community Development Programming MySQL and Server Configuration Selection by Month in UNIX Time?

How can I select records based on a month or a year if the timestamp is in UNIX time? I can convert a single variable in UNIX time no problem but what is the best way to search a table looking for all records time stamped in September for example.

Here is a sample table:

1|1076460180|5004|Ed Husar|[email protected]|0
2|1076460363|4005|Ed Husar|[email protected]|0

Thanks in advance,

Ed

you need to variables.
One for the timestamp of sept 1st and one for sept 31th.
Then perform a query like that: SELECT * FROM table where timestamp > $var1 and timestamp < $var2

Here was what I ended up with. It appears to work but I need to add data with a few different months timestamp to verify.

// These are my start and end data filter
$date1="02/01/2004";
$date2="02/29/2004";

// Now we convert the dates
$time1=strtotime($date1);
$time2=strtotime($date2);

// select statement from HELL!
$result = mysql($database, "select * from ezu_files a, ezu_uploads b, ezu_uploadinfos c WHERE b.id = a.upload AND a.type='i-world/i-vrml' AND b.id = c.upload AND c.name='Description' AND b.uploaded between '$time1' and '$time2'");

I am not sure how efficient the select statement is but I don't know of a better way. That is another post I guess.

Cheers,

Ed

You might want to look at the MySQL date functions:

http://www.mysql.com/doc/en/Date_and_time_functions.html

You might be able to use FROM_UNIXTIME to get a month and year during the select...